package com.ahao.dao;

import com.ahao.model.Course;
import com.ahao.utils.DBUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

public class CourseDao {
    QueryRunner queryRunner = new QueryRunner(DBUtils.getDataSource());

    /**
     * 查询所有课程
     *
     * @return
     * @throws SQLException
     */
    public List<Course> getAllCourses() throws SQLException {
        return queryRunner.query("select * from course", new BeanListHandler<>(Course.class));
    }

    /**
     * 根据课程名查询课程
     *
     * @param coursrName
     * @return
     */
    public Course getCourseByName(String coursrName) throws SQLException {
        return queryRunner.query("select * from course where coursename=?", new BeanHandler<>(Course.class), coursrName);
    }

    /**
     * 添加课程
     */
    public Integer addCourse(String courseName) throws SQLException {
        return queryRunner.update("insert into course(coursename) values(?)", courseName);
    }

    /**
     * 根据课程id查询课程
     * @param cid
     * @return
     * @throws SQLException
     */
    public Course selectCourseByCid(Integer cid) throws SQLException {
        return queryRunner.query("select * from course where cid=?", new BeanHandler<>(Course.class), cid);
    }

    /**
     * 根据课程id删除课程
     * @param cid
     * @return
     * @throws SQLException
     */
    public Integer deleteCourseByCid(Integer cid) throws SQLException {
        return queryRunner.update("delete from course where cid=?", cid);
    }

    public List<Course> getCoursesByGid(int gid) throws SQLException {
        return queryRunner.query("SELECT c.* FROM course c,grade_course gc WHERE c.`cid`=gc.`cid` AND gc.gid=?", new BeanListHandler<>(Course.class), gid);
    }
}
